//
//  JCLSQLiteManager.swift
//  01-数据库
//
//  Created by DDC on 2017/9/29.
//  Copyright © 2017年 DDC. All rights reserved.
//

import Foundation
import FMDB

// 数据保存的最大时间
fileprivate let maxCacheTime:TimeInterval = -60//60 * 60 * 24 * (-5)//测试一分钟之前的时间  


class JCLSQLiteManager {
    
    /// 创建单例
    static let shared = JCLSQLiteManager()
    /// 创建队列
    var queue: FMDatabaseQueue?
    /// 私有初始化方法
    fileprivate init() {
        // 创建数据库路径
        var path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0]
        path = (path as NSString).appendingPathComponent("status.db")
        
        queue = FMDatabaseQueue(path: path)
        print(path)
        // 创建表格
        createTable()
        // 发送通知--清除缓存
        NotificationCenter.default.addObserver(self, selector: #selector(clearDBCache), name: NSNotification.Name.UIApplicationDidEnterBackground, object: nil)
    
    }
    // 移除通知
    deinit {
        NotificationCenter.default.removeObserver(self)
    }
    @objc fileprivate func clearDBCache(){
        let dateString = Date.jcl_dateToString(delta: maxCacheTime)
        print("清除缓存\(dateString)")
        // 清除缓存
        // 1. 准备SQL
        let sql = "DELETE FROM T_Status WHERE createTime < ?;"
        // 2. 执行SQL
        queue?.inDatabase({ (db) in
            
            if db?.executeUpdate(sql, withArgumentsIn: [dateString]) == true {
                print("成功删除了\(db?.changes())条数据")
            }
        })
    }
    
}

// MARK: - 对表格的增删改查
extension JCLSQLiteManager {
    
    // 加载微博数据
    func loadStatus (userId:String,since_id:Int64 = 0,max_id:Int64 = 0) -> [[String:Any]] {
        
        // 拼接sql
        var sql = "SELECT statusId, userId, status FROM T_Status \n"
            sql += "WHERE userId = \(userId) \n"
        if since_id > 0 {
            sql += "AND statusId > \(since_id) \n"
        }
        if max_id > 0 {
            sql += "AND statusId < \(max_id) \n"
        }
            sql += "ORDER BY statusId LIMIT 20;"
        // 通过sql来查询数据
        let rs = extcRecords(sql: sql)
        // 可变数组
        var result = [[String:Any]]()
        for dict in rs {
            // 将数组数据反序列化
            guard let jsonData = dict["status"] as? Data,
            let json = try? JSONSerialization.jsonObject(with: jsonData, options: []) as? [String:Any]
            else {
                continue
            }
            // 追加进数组
            result.append(json ?? [:])
            
        }
        // 返回
        return result

    }
    
    func updateStatus ( userId:String,array:[[String:Any]]) {
        
        let sql = "INSERT OR REPLACE INTO T_Status (statusId,userId,status) VALUES (?,?,?);"
        
        queue?.inTransaction({ (db, rollback) in
            
            for dict in array {
                
                guard let statusId = dict["idstr"],
                
                    let jsonData = try? JSONSerialization.data(withJSONObject: dict, options: []) else {
                        continue
                }
                if db?.executeUpdate(sql, withArgumentsIn: [statusId,userId,jsonData]) == false {
                    
                    // 需要回滚
                    rollback?.pointee = true
                    break
                }
             }
        })
    }
    
    // 查询数据
    func extcRecords(sql:String) -> [[String:Any]] {
        
        
        var result = [[String:Any]]()
        queue?.inDatabase({ (db) in
            
            guard let rs = db?.executeQuery(sql, withArgumentsIn: []) else {
                return
            }
            while rs.next() {
                
                let colCount = rs.columnCount()
                for col in 0..<colCount {
                    
                    guard let name = rs.columnName(for: col),
                        let value = rs.object(forColumnIndex: col) else {
                            return
                    }
                    
                    result.append([name:value])
                }
                
            }
        })
        return result
    }
}
// MARK: - 创建表格和私有方法
extension JCLSQLiteManager {
 
    /// 创建表格
    func createTable () {
        // 守护路径
        guard let path = Bundle.main.path(forResource: "status.sql", ofType: nil),
        // 从bundle加载sql文件
        let sql = try? String(contentsOfFile: path) else {
            return
        }
       
        // 执行SQL
        // FMDB  是串行队列，同步执行的，可以保证在多个操作的同时，只有一个队列对数据库进行操作
        queue?.inDatabase({ (db) in
            if db?.executeStatements(sql) == true {
               print("建表成功")
            } else{
                print("建表失败")
            }
        })
    }
    
    
}
